package com.flca.frw.dao.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.flca.frw.util.FileUtils;

public class SqlExecutor {
	protected static Logger logger = LoggerFactory.getLogger(SqlExecutor.class);

	private Connection connection;

	private EntityManager entityMgr;

	private Map<String, String> substitutes;

	private static final String DELIM = ";";

	public SqlExecutor(Connection aConnection) throws NullPointerException {
		super();
	}


	/**
	 * Use this method to exec all sql statements in the given file. The sql
	 * statements should be seperated by a colon. It is possible to substitute
	 * the sql statements with the aSubstitutes map.
	 * 
	 * @param iPersMgr
	 * @param aFilename
	 * @param aSubstitutes
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public int exec(String aFilename, Map<String, String> aSubstitutes) throws FileNotFoundException, IOException, SQLException {
		String allSqls = FileUtils.readFile(aFilename);
		return doExecuteSqls(allSqls);
	}

	/**
	 * Use this method to exec all sql statements in the given resource. The sql
	 * statements should be seperated by a colon. It is possible to substitute
	 * the sql statements with the aSubstitutes map.
	 * 
	 * @param iPersMgr
	 * @param aFilename
	 * @param aCaller
	 * @param aSubstitutes
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public int exec(String aResourceName, Object aCaller, Map<String, String> aSubstitutes) throws FileNotFoundException, IOException,
			NullPointerException, SQLException {
		substitutes = aSubstitutes;

		String allSqls = FileUtils.readResource(aCaller, aResourceName);
		return doExecuteSqls(allSqls);
	}

	/**
	 * execute the given sql update statement(s)
	 * 
	 * @param aSqlStatements
	 * @return
	 * @throws SQLException
	 */
	public int execSql(String aSqlStatements) throws SQLException {
		return doExecuteSqls(aSqlStatements);
	}

	/**
	 * execute a sql "select count ..." stament
	 * 
	 * @param aSqlStatements
	 * @return
	 * @throws SQLException
	 */
	public int countSql(String aSqlStatements) throws SQLException {
		return doCountSql(aSqlStatements);
	}

	private int doExecuteSqls(String aAllSqls) throws SQLException {
		String sqls[] = StringUtils.split(aAllSqls + DELIM, DELIM);

		int result = 0;
		SQLException sqlException = null;

		for (String sql : sqls) {
			try {
				doExecuteSql(sql);
				result++;
			} catch (SQLException e) {
				logger.error("error executing " + sql + " ", e);
				sqlException = e;
			}
		}

		if (result == 0 && sqlException != null) {
			throw sqlException;
		}
		return result;
	}

	private void doExecuteSql(String aSql) throws SQLException {
		String sql = aSql;

		sql = doSubstitute(sql);

		logger.info("executing: " + aSql);

		if (entityMgr != null) {
			Query qry = entityMgr.createNativeQuery(aSql);
			entityMgr.getTransaction().begin();
			qry.executeUpdate();
			entityMgr.getTransaction().commit();
		} else if (connection != null) {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(sql);
		}
	}

	private String doSubstitute(String sql) {
		if (substitutes != null && substitutes.size() > 0) {
			for (String subsFrom : substitutes.keySet()) {
				String subsTo = substitutes.get(subsFrom);
				sql = StringUtils.replace(sql, subsFrom, subsTo);
			}
		}
		return sql;
	}

	private int doCountSql(String aSql) throws SQLException {
		int result = -1;

		if (entityMgr != null) {
			Query qry = entityMgr.createNativeQuery(aSql);
			// entityMgr.getTransaction().begin();
			Object obj = qry.getSingleResult();
			if (obj != null) {
				result = Integer.parseInt(obj.toString());
			}
			// entityMgr.getTransaction().commit();
		} else if (connection != null) {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(aSql);
			if (rs != null) {
				if (rs.first()) {
					return rs.getInt(1);
				}
			}
		}
		return result;
	}

	public List<?> select(String aSql) {
		if (entityMgr != null) {
			Query qry = entityMgr.createNativeQuery(aSql);
			return qry.getResultList();
		} else {
			return null;
		}
	}
}
